﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms.Form
Imports Microsoft.Office.Interop
Imports System.IO
Module HamXuLy
    '<<<<Lưu hồ sơ scan>>>>
    Public Sub StorePicture(sohoso As Integer, filename As String)
        Dim imageData As Byte() = Nothing
        Using fs As New FileStream(filename, FileMode.Open, FileAccess.Read)
            imageData = New Byte(fs.Length - 1) {}
            fs.Read(imageData, 0, CInt(fs.Length))
        End Using
        Dim cmd As New SqlCommand("InsertImageCG", ob_cnn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@sohoso", sohoso)
        cmd.Parameters("@sohoso").Direction = ParameterDirection.Input
        cmd.Parameters.AddWithValue("@tenname", filename.Split("\\").Last())
        cmd.Parameters("@tenname").Direction = ParameterDirection.Input
        cmd.Parameters.Add("@hinhanh", SqlDbType.Image)
        cmd.Parameters("@hinhanh").Direction = ParameterDirection.Input
        cmd.Parameters("@hinhanh").Value = imageData
        ob_cnn.Open()
        cmd.ExecuteNonQuery()
        ob_cnn.Close()
    End Sub
    Public Function RetrieveImage(tenfile As String, sohoso As Integer) As Byte()
        ob_cnn.Open()
        Dim cmd As New SqlCommand("select hinhanh from LuuHinhTam where tenfile = '" & tenfile & "' and sohoso = " & sohoso & "", ob_cnn)
        Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
        If Not imageData Is Nothing Then
            Using ms As New MemoryStream(imageData, 0, imageData.Length)
                ms.Write(imageData, 0, imageData.Length)
            End Using
        End If
        ob_cnn.Close()
        Return imageData
    End Function
    Public Function RetrieveImage1(tenfile As String, id As Integer) As Byte()

        Dim cmd As New SqlCommand("select anh from Luufile where Luunoidung = '" & tenfile & "' and id = " & id & "", ob_cnn)
        Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
        If Not imageData Is Nothing Then
            Using ms As New MemoryStream(imageData, 0, imageData.Length)
                ms.Write(imageData, 0, imageData.Length)
            End Using
        End If

        Return imageData
    End Function
    Public Function RetrieveImage2(tenfile As String, id As Integer) As Byte()

        Dim cmd As New SqlCommand("select noidung from LuuTepScanBienDong where tentep = '" & tenfile & "' and maid = " & id & "", ob_cnn)
        Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
        If Not imageData Is Nothing Then
            Using ms As New MemoryStream(imageData, 0, imageData.Length)
                ms.Write(imageData, 0, imageData.Length)
            End Using
        End If

        Return imageData
    End Function
    Public Function LoadVaoGridview(sohoso As Integer) As DataTable
        ob_cnn.Open()
        Dim cmd As New SqlCommand("select sohoso, tenfile from LuuHinhTam where sohoso = @sohoso", ob_cnn)
        cmd.Parameters.AddWithValue("@sohoso", sohoso)
        Dim dt As New DataTable()
        dt.Load(cmd.ExecuteReader())
        ob_cnn.Close()
        Return dt
    End Function
    Public Sub XoafileTam(dirTemp As String)

        Try
            Dim fileNames As String() = Directory.GetFiles(dirTemp & "\\Quan Ly Kho\\", "*.*")
            For Each _file As String In fileNames
                Try
                    File.Delete(_file)
                Catch ex As Exception
                    MessageBox.Show("Vui lòng tắt các file bạn đang mở !")
                    Return
                End Try
            Next

        Catch ex As Exception

        End Try
    End Sub
    Public Function KiemTraFile(sohoso As Integer, tenFile As String) As Boolean

        Dim cmd As New SqlCommand("select * from LuuHinhTam where sohoso = @sohoso and TenFile = @tenfile", ob_cnn)
        cmd.Parameters.AddWithValue("@sohoso", sohoso)
        cmd.Parameters.AddWithValue("@tenfile", tenFile)
        Dim dt As New DataTable()
        ob_cnn.Open()
        dt.Load(cmd.ExecuteReader())
        If dt.Rows.Count > 0 Then
            ob_cnn.Close()
            Return True
        End If
        ob_cnn.Close()
        Return False
    End Function
    '<<<< Kết thúc Lưu hồ sơ scan>>>>
    Public Function KiemTraKyTu(ByVal Str As String)
        For Each kytu As Char In Str
            If IsNumeric(kytu) = False Then
                Return False
            End If
        Next
        Return True
    End Function
    Public Function KiemTraSoAm(ByVal Str As String)
        If Str.StartsWith("-") Or Str.Contains(",") Then
            Return False
        End If
        Return True
    End Function
    Public Function LoadThongTin(sql As String) As DataTable
        Dim cmd As SqlCommand
        Dim da As SqlDataAdapter
        Dim tb As DataTable

        tb = New DataTable
        Try
            cmd = New SqlCommand(sql, ob_cnn)
            da = New SqlDataAdapter(cmd)
            da.Fill(tb)
        Catch ex As Exception
            MessageBox.Show("Lỗi. Sai thông tin ", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
            ob_cnn.Close()
        End Try

        Return tb
    End Function
    Public Sub XuatExcelSTD()
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "D:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        '  Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")

        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process


        Try
            With Excel
                '.SheetsInNewWorkbook = 1
                .Workbooks.Open(Application.StartupPath & "\Template\MAU_STD.dll")
                .Worksheets(1).Select()
                .Cells.Font.Size = 12
                .Cells.Font.Name = "Times New Roman"
                Dim i As Integer
                Dim j As Integer
                Dim sql As String = "SELECT chusd1, ngaybd, tobd, sothua1, noidungbd FROM sobiendong "
                ' SqlAdapter
                Dim dscmd As New SqlDataAdapter(sql, ob_cnn)
                ' DataSet
                Dim ds As New DataSet
                dscmd.Fill(ds)

                For i = 0 To ds.Tables(0).Rows.Count - 1
                    'Column
                    For j = 0 To ds.Tables(0).Columns.Count - 1
                        ' this i change to header line cells 
                        .Cells(i + 5, j + 2) = ds.Tables(0).Rows(i).Item(j)
                    Next
                    .Cells(i + 5, 1) = i + 1
                Next
                .ActiveCell.Worksheet.SaveAs(FileName)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Dữ liệu đã xuất sang Excel thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
    Public Sub LoadHuyen(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From TENHUYEN"

        cmb.ValueMember = "MaH"
        cmb.DisplayMember = "T_Huyen"
        cmb.DataSource = LoadThongTin(sql)

    End Sub
    Public Sub LoadTenVanBan(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMTenVanBan  order by TenVanBan"

        cmb.ValueMember = "MaTenVanBan"
        cmb.DisplayMember = "TenVanBan"
        cmb.DataSource = LoadThongTin(sql)

    End Sub
    Public Sub LoadXa(_MaHuyen As Integer, ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From TENXA Where MaHuyen = " & _MaHuyen & " "

        cmb.ValueMember = "MaXa"
        cmb.DisplayMember = "TenXA"
        cmb.DataSource = LoadThongTin(sql)

    End Sub
    Public Sub LoadPhong(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMPHONG"

        cmb.ValueMember = "MaPhongID"
        cmb.DisplayMember = "TenPhong"
        cmb.DataSource = LoadThongTin(sql)

    End Sub

    Public Sub LoadKe(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMKE"

        cmb.ValueMember = "MaKeID"
        cmb.DisplayMember = "TenKe"
        cmb.DataSource = LoadThongTin(sql)

    End Sub

    Public Sub LoadKho(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMKHO"

        cmb.ValueMember = "MaKhoID"
        cmb.DisplayMember = "TenKho"
        cmb.DataSource = LoadThongTin(sql)

    End Sub

    Public Sub LoadTinhTrang(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMTINHTRANG"

        cmb.ValueMember = "MaTinhTrangID"
        cmb.DisplayMember = "TenTinhTrang"
        cmb.DataSource = LoadThongTin(sql)

    End Sub
    Public Sub LoadHinhThuc(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMHINHTHUC"

        cmb.ValueMember = "MaHinhThucID"
        cmb.DisplayMember = "TenHinhThuc"
        cmb.DataSource = LoadThongTin(sql)

    End Sub
    Public Sub LoadNguongoc(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From LoaiNguonGocSuDung"

        cmb.ValueMember = "MaNguongocSudungdat"
        cmb.DisplayMember = "TenNguongocSudungdat"
        cmb.DataSource = LoadThongTin(sql)

    End Sub

    Public Function AutoID(strTable As String) As Integer
        Dim sql = "Select IDENT_CURRENT('" & strTable & "' )"
        Dim tb As New DataTable
        tb = LoadThongTin(sql)

        Return Integer.Parse(tb.Rows(0)(0).ToString)

    End Function


    'Thao tac du lieu = update, insert, delete
    Public Function ThaoTacDuLieu(sql As String) As Boolean
        Dim cmd As SqlCommand
        Try
            If (ob_cnn.State = ConnectionState.Closed) Then
                ob_cnn.Open()
            End If

            cmd = New SqlCommand(sql, ob_cnn)
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            Return True
        Catch ex As Exception
            MessageBox.Show("Thao tác không hợp lệ")
            ob_cnn.Close()
            Return False
        End Try



    End Function
    Public Sub XuatExcel(dgv As DataGridView, sheetName As String)
        ' xuat truc tiep ra excel
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "D:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")

        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()
                .Worksheets(1).name = sheetName
                Dim i As Integer = 1
                For col = 0 To dgv.Columns.Count - 1
                    .cells(1, i).value = dgv.Columns(col).HeaderText
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To dgv.Columns.Count - 1
                    i = 2
                    For row = 0 To dgv.Rows.Count - 1
                        With .worksheets(1)
                            .Cells(i, k).Value = dgv.Rows(row).Cells(col).Value
                            i += 1
                        End With
                    Next
                    k += 1
                Next
                .ActiveCell.Worksheet.SaveAs(FileName)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Dữ liệu xuất ra Excel thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next
    End Sub
    Public Sub ExportExcel(dgv As DataGridView)
        Const CLSE = "Excel.Application"

        Dim rowsTotal, colsTotal As Short  'Tong so dong, so cot
        Dim i, j, iC As Short 'Cac bien chay

        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor

        Dim ExcelApp As Object 'Excel.Application
        Dim ExcelWBook As Object 'Excel.Wordbook
        Dim ExcelWSheet As Object 'Excel.Worksheet
        Try
            'If ExcelApp Is Nothing Then
            ExcelApp = CreateObject(CLSE)
            'End If

            ExcelWBook = ExcelApp.Workbooks.Add
            ExcelWSheet = ExcelWBook.Sheets("Sheet1")

            ExcelApp.DisplayAlerts = False
            rowsTotal = dgv.RowCount            'So dong cua DataGridview
            colsTotal = dgv.Columns.Count       'So dong cua DataGridview

            Dim arr(rowsTotal, colsTotal) As Object

            Dim tb As DataTable
            tb = dgv.DataSource
            'Chuyển dữ liệu từ DataTable vào mảng đối tượng
            For i = 0 To rowsTotal - 1
                For j = 0 To colsTotal - 1
                    arr(i, j) = tb.Rows(i)(j)
                Next

            Next
            Dim rowStart, columnStart, rowEnd, columnEnd As Integer

            rowStart = 2
            columnStart = 1
            rowEnd = rowStart + rowsTotal - 1
            columnEnd = columnStart + colsTotal - 1

            With ExcelWSheet
                .Cells.Select()
                .Cells.Delete()

                'Tao dong tieu de
                For iC = 0 To colsTotal - 1
                    .Cells(1, iC + 1).Value = dgv.Columns(iC).HeaderText
                Next
                'Dinh dang Vung tieu de
                Dim head As Microsoft.Office.Interop.Excel.Range
                head = .Range(.Cells(1, 1), .Cells(1, columnEnd))
                head.Font.FontStyle = "Bold"
                head.Font.Size = 11
                head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
                head.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid

                Dim data As Microsoft.Office.Interop.Excel.Range
                data = .Range(.Cells(rowStart, columnStart), .Cells(rowEnd, columnEnd))

                data.Value2 = arr
                data.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid


                .Cells.Columns.AutoFit()
                .Cells.Columns.Font.Name = "Times New Roman"
                .Cells.Select()
                .Cells.EntireColumn.AutoFit()
                .Cells(1, 1).Select()

                ExcelApp.Visible = True

            End With

        Catch ex As Exception
            MsgBox("Xuất Excel lỗi " & ex.Message)
        Finally
            'RELEASE ALLOACTED RESOURCES
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
            ExcelApp = Nothing
        End Try
    End Sub

    Public Sub ExportWord(ts() As String, gt() As String, hosocungcap As String, sophieu As String)
        Const CLSO = "Word.Application"
        Const CLSD = "Word.Document"
        Const wdReplaceAll = 2
        Dim WordApp As Object 'Word.Application
        Dim WordDoc As Object 'Word.Document
        Dim Selection As Object 'Word.Application.Selection
        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
        Try
            'If WordApp Is Nothing Then
            WordApp = CreateObject(CLSO)
            'End If
            WordApp.Visible = True
            WordDoc = CreateObject(CLSD)
            WordApp.Visible = False
            WordDoc = WordApp.Documents.Open(Application.StartupPath & "\Template\" & hosocungcap & ".dll")

            'Tim kiem va thay the cac bien

            Selection = WordApp.Selection
            Dim i As Integer
            For i = 0 To gt.Length - 1

                Selection.Find.ClearFormatting()
                Selection.Find.Replacement.ClearFormatting()
                With Selection.Find
                    .Text = ts(i) 'Tu can tim
                    .Replacement.Text = gt(i)   'Tu thay the
                    .Forward = True  'Ve phia truoc
                    .MatchCase = True
                    .MatchWholeWord = True 'True
                    .Execute(Replace:=wdReplaceAll)
                End With
            Next
            Dim strNewDirPath As String = "D:\InPhieu"
            If Not System.IO.Directory.Exists(strNewDirPath) Then
                System.IO.Directory.CreateDirectory(strNewDirPath)
            End If
            WordDoc.SaveAs("" & strNewDirPath & "\" & hosocungcap & "_So-" & sophieu & ".doc")
            MessageBox.Show("In phiếu thành công !" & vbCrLf & "Phiếu được lưu tại: " & strNewDirPath & ".")
            WordApp.Visible = True
            WordApp.Activate()
        Catch ex As Exception
            MessageBox.Show("In phiếu bị lỗi: " & ex.Message)
        Finally
            WordApp = Nothing
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
        End Try
    End Sub
    Public Function GetTenNguoiNhap(_tenDangNhap As String) As String
        Dim sql As String
        Try
            sql = "SELECT hotenNguoiDangNhap FROM LOGIN WHERE tenDangNhap like '" & _tenDangNhap & "' "
            Return LoadThongTin(sql).Rows(0)(0).ToString
        Catch
            Return ""
        End Try
    End Function

End Module
